package com.cpi.cornpp.distributionfutureversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.distributionfutureversion.dto.DistributionFutureVersionDTO;
import com.cpi.cornpp.distributionfutureversion.dto.DistributionFutureVersionDetailDTO;
import com.cpi.cornpp.distributionfutureversion.exception.DistributionFutureVersionException;
import com.cpi.cornpp.purchase.program.exception.PurchaseProgramException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class DistributionFutureVersionDAO extends DBDAO {
	private static final String SQL_EXECUTE_SP_CREATE_DISTRIBUTION = "EXEC sp_adm_Insert_PurOrderFutureVersions ?";
	private static final String SQL_EXECUTE_SP_REMOVE_DISTRIBUTION = "EXEC sp_adm_Delete_PurOrderFutureVersions ?";
	private static final String SQL_QUERY_FUTURES_BY_VERSION = 
			"select	fp.future_period_id, " +
					"fp.short_name + ' ' + convert( varchar, fp.year ) periodDescription, " +
					" (select count(*) from biweeks_periods_versions bpv inner join biweeks_periods bp on bpv.biweek_period_id = bp.biweek_period_id inner join purchases_orders_versions pov on bpv.biweek_period_version_id = pov.biweek_period_version_id and is_transit = 0 where bpv.version_id = v.version_id and bp.start_date+1 between fp.start_date and fp.end_date) as purchaseOrderNum, "+
					" isnull( count(distinct pofv.purchase_order_version_id), 0 ) purchaseOrderAssignedNum, " +
					" (select sum(pov.capacity) from biweeks_periods_versions bpv inner join biweeks_periods bp on bpv.biweek_period_id = bp.biweek_period_id inner join purchases_orders_versions pov on bpv.biweek_period_version_id = pov.biweek_period_version_id and is_transit = 0 where bpv.version_id = v.version_id and bp.start_date+1 between fp.start_date and fp.end_date) as tonsTotal, " +
					" (select sum( quantity ) from futures_periods_versions inner join corn_futures_versions on future_period_version_id = application_future_period_version_id where version_id = v.version_id and future_period_id = fp.future_period_id ) as actuals, " +
					" sum( pofv.quantity ) futAssigned " +
			"from	versions v " +
						"left join futures_periods fp " +
						"on fp.year between year(v.start_date) and year(v.end_date) " +
						"left join futures_periods_versions fpv " +
						"on fp.future_period_id = fpv.future_period_id and " +
						 "  v.version_id = fpv.version_id " +
						"left join corn_futures_versions cfv " +
						"on fpv.future_period_version_id = cfv.application_future_period_version_id " +
						"left join purchases_orders_futures_versions pofv " +
						"on cfv.corn_future_version_id = pofv.corn_future_version_id " +
			"where	v.version_id = ? " +
			"group by v.version_id, fp.future_period_id, " +
					 "fp.short_name, " +
					 "fp.year, " +
					 "fp.start_date," +
					 "fp.end_date " +
			"order by fp.start_date, " +
					 "fp.year";
	
	
	private static final String SQL_QUERY_FUTURES_DETAIL_BY_FUTURE_PERIOD_ID = 
		"select	pov.purchase_order_version_id as id, " +
		"			pov.code  as code," +
		"			pov.code_number as codeNumber," +
		"			pov.capacity as capacity," +
		"			bp.description as period," +
		"			CONVERT(INTEGER, pov.capacity*(select qty from units where unit_id = 'CON')+ .99) as futRequired," +
		"			(select sum(quantity) from purchases_orders_futures_versions where purchase_order_version_id = pov.purchase_order_version_id) as futAssigned,"+ 
		"			avg.weight_avg_price  as price," +
		"			pofv.quantity as futAssignedTotal," +
		"			cfv.unit_price as unitPrice" +
		"	from	versions v" +
		"				inner join futures_periods_versions fpv" +
		"				on v.version_id = fpv.version_id" +
		"				inner join corn_futures_versions cfv" +
		"				on fpv.future_period_version_id = cfv.application_future_period_version_id" +
		"				inner join purchases_orders_futures_versions pofv" +
		"				on cfv.corn_future_version_id = pofv.corn_future_version_id" +
		"				inner join purchases_orders_versions pov" +
		"				on pofv.purchase_order_version_id = pov.purchase_order_version_id and " +
		"                   is_transit = 0 "+
		"               left join vw_pov_futures_weight_avg_price avg "+
		"               on avg.purchase_order_version_id =pov.purchase_order_version_id "+
		"				inner join biweeks_periods_versions bpv" +
		"				on bpv.biweek_period_version_id = pov.biweek_period_version_id and" +
		"				   bpv.version_id = v.version_id" +
		"				inner join biweeks_periods bp" +
		"				on bpv.biweek_period_id = bp.biweek_period_id" +
		"               inner join transports t "+
		"               on t.transport_id = pov.transport_id "+
		"	where	v.version_id = ? and" +
		"			fpv.future_period_id = ? " +
		"   order by bp.start_date, " +
		"				 t.priority desc, " +
		"				 pov.code, " +
		"				 pov.code_number, "+
		"                unit_price desc ";
	/**
	 * Metodo que agrega una distribucion a una version
	 * 
	 * @author Edgar Joao
	 * @param versionId
	 * @throws PurchaseProgramException
	 */
	public void addDistribution(int versionId) throws DistributionFutureVersionException{
		PreparedStatement pstm = null;		
		StringBuffer sql = null;
		try{
			sql = new StringBuffer(0);
			sql.append(SQL_EXECUTE_SP_CREATE_DISTRIBUTION);
			pstm = prepareSQL(sql.toString(), dsName);
			pstm.setInt(1, versionId);
			executeInsert(pstm);
		} catch(PoolException poe){
			DistributionFutureVersionException distributionFutureVersionException = null;
			poe.printStackTrace(System.out);
			distributionFutureVersionException = new DistributionFutureVersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw distributionFutureVersionException;
		} catch(SQLException sqle){
			DistributionFutureVersionException distributionFutureVersionException = null;
			sqle.printStackTrace(System.out);
			distributionFutureVersionException = new DistributionFutureVersionException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw distributionFutureVersionException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	/**
	 * Metodo que elimina una distribucion de una version
	 * 
	 * @author Edgar Joao
	 * @param versionId
	 * @throws PurchaseProgramException
	 */
	public void delDistribution(int versionId) throws DistributionFutureVersionException{
		PreparedStatement pstm = null;		
		StringBuffer sql = null;
		try{		
			sql = new StringBuffer(0);
			sql.append(SQL_EXECUTE_SP_REMOVE_DISTRIBUTION);
			pstm = prepareSQL(sql.toString(), dsName);
			pstm.setInt(1, versionId);
			executeInsert(pstm);
			
		} catch(PoolException poe){
			DistributionFutureVersionException distributionFutureVersionException = null;
			poe.printStackTrace(System.out);
			distributionFutureVersionException = new DistributionFutureVersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw distributionFutureVersionException;
		} catch(SQLException sqle){
			DistributionFutureVersionException distributionFutureVersionException = null;
			sqle.printStackTrace(System.out);
			distributionFutureVersionException = new DistributionFutureVersionException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw distributionFutureVersionException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	/**
	 * 
	 * @param versionId
	 * @return
	 * @throws DistributionFutureVersionException
	 */
	public List getDistributionFuturesByVersion(Integer versionId) throws DistributionFutureVersionException{
		List dtoDistributionFuturesList = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;		
		
		dtoDistributionFuturesList = new ArrayList(0);
		
		try{
			
			if (versionId != null){
				
				pstm = prepareSQL(SQL_QUERY_FUTURES_BY_VERSION, dsName);
				
				pstm.setInt(1, versionId.intValue());
				
				rs = executeQuery(pstm);
				dtoDistributionFuturesList = mapRows(rs);
			}
			
		} catch(PoolException poe){
			DistributionFutureVersionException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new DistributionFutureVersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			DistributionFutureVersionException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new DistributionFutureVersionException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return dtoDistributionFuturesList;
		
	}
	
	/**
	 * 
	 * @param rs
	 * @param index
	 * @return
	 * @throws SQLException
	 */
	private DistributionFutureVersionDTO mapRow(ResultSet rs, int index) throws SQLException{
		DistributionFutureVersionDTO dto = null;
		
		if ((rs != null) && (rs.absolute(index))){
			dto = new DistributionFutureVersionDTO();
			dto.setFuturePeriodId((Integer) rs.getObject("future_period_id"));
			dto.setPeriodDescription((String) rs.getObject("periodDescription"));
			dto.setPurchaseOrderNum((Integer) rs.getObject("purchaseOrderNum"));
			dto.setTonsTotal(new Double(rs.getDouble("tonsTotal")));
			dto.setFutActual(new Integer(rs.getInt("actuals")));
			dto.setFutAssigned(new Integer( rs.getInt("futAssigned") ));
		}
		
		return dto;
	}
	
	/**
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List mapRows(ResultSet rs) throws SQLException{
		List dtoList = null;
		
		dtoList = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				dtoList.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return dtoList;
	}
	
	/**
	 * 
	 * @param versionId
	 * @return
	 * @throws DistributionFutureVersionException
	 */
	public List getDistributionFutureDetailByFuturePeriodId(Integer futurePeriodId, Integer versionId) throws DistributionFutureVersionException{
		List distributionFutureVersionDetailDTOs = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;		
		
		distributionFutureVersionDetailDTOs = new ArrayList(0);
		
		try{
			
			if (futurePeriodId != null){
				
				pstm = prepareSQL(SQL_QUERY_FUTURES_DETAIL_BY_FUTURE_PERIOD_ID, dsName);
				
				pstm.setInt(1, versionId.intValue());
				pstm.setInt(2, futurePeriodId.intValue());
				
				rs = executeQuery(pstm);
				distributionFutureVersionDetailDTOs = mapDetailRows(rs);
			}
			
		} catch(PoolException poe){
			DistributionFutureVersionException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new DistributionFutureVersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			DistributionFutureVersionException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new DistributionFutureVersionException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}		
		return distributionFutureVersionDetailDTOs;		
	}	

	
	/**
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List mapDetailRows(ResultSet rs) throws SQLException{
		List dtoList = null;
		
		dtoList = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				dtoList.add(mapDetailRow(rs, rs.getRow()));
			}
		}
		
		return dtoList;
	}
	
	/**
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private DistributionFutureVersionDetailDTO mapDetailRow(ResultSet rs, int index) throws SQLException{
		DistributionFutureVersionDetailDTO dto = null;
		
		if ((rs != null) && (rs.absolute(index))){
			dto = new DistributionFutureVersionDetailDTO();
			dto.setId(rs.getString("id"));
			dto.setCode(rs.getString("code").trim());
			dto.setCodeNumber((Integer)rs.getObject("codeNumber"));
			dto.setTons(new Double(rs.getDouble("capacity")));
			dto.setPeriodDescription(rs.getString("period"));
			dto.setFuturesRequired(new Integer( rs.getInt("futRequired") )) ;
			dto.setFuturesAssigned((Integer) rs.getObject("futAssigned"));
			dto.setBalanced(new Double(rs.getDouble("price")));
			dto.setFutures((Integer) rs.getObject("futAssignedTotal"));
			dto.setPrice(new Double(rs.getDouble("unitPrice")));
		}
		
		return dto;
	}
	
	
}